IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCOIUserHierarchyForSuperUser]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCOIUserHierarchyForSuperUser]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO   
/******************************************************************                                    
* Name:   [GetCOIUserHierarchyForSuperUser]                                    
* Purpose:  Retrieves COIIds matching the filter parameters passed in using multiple  table joins                                     
*                                    
* PARAMETERS                                    
* Name          Description                                         
* -------------      -------------------------------------------                                    
* Name:   [GetCOIUserHierarchyForSuperUser]                            
* Purpose:  Retrieves COIIds matching the filter parameters passed in using multiple  table joins                             
* @admPersonId      Domain User ID (REQUIRED)                                      
* @firstName      FirstName of the user.                                    
* @lastName       LastName for the user.
* @supervisorFirstName  Supervisor firstName                               
* @supervisorLastName  Supervisor last name                                    
* @userName       UserName for the user.                                    
* @isPolicyQuestionSelected   Determines wheteher or not the result should bring    
  back only COIs with policy questions.                                    
* @isNothingToDiscloseSelected Determines wheteher or not the result should bring 
  back only COIs with Nothing to disclose.                                    
* @statusXml     XML string containing the selected status.                                
* @recursive      determines whether to bring back the entire descENDants or the 
  direct descENDants                              
* @SortColumn  Column to sort on                                 
* @sortOrder   Determines whether or not the sort should be performed in ascENDing 
  or descENDing order.          
* @hasExistingRelation  Determines whether or not to bring the inactive relation    
* @HierarchyTypeId. Users Hierarchy Type.     
*********************************************************************/                                    
CREATE PROCEDURE [dbo].[GetCOIUserHierarchyForSuperUser]                                       
(                                        
	@admPersonId INT,                                        
	@firstName  VARCHAR(64)= NULL,                                        
	@lastName VARCHAR(64)= NULL, 
	@supervisorFirstName  VARCHAR(64) = NULL,                               
	@supervisorLastName  VARCHAR(64) = NULL,                                         
	@userName VARCHAR(36) = NULL,                                        
	@isPolicyQuestionSelected BIT = 0,                                        
	@isNothingToDiscloseSelected BIT = 0,                                        
	@statusXml TEXT = NULL,                                         
	@recursive BIT = 1,                                
	@sortColumn VARCHAR(150)= 'LastName',                              
	@sortOrder BIT = 1, -->Asc,0-->Desc                                   
	@hasExistingRelation BIT = 0,
	@HierarchyTypeId INT = 0                 
)                                        
AS BEGIN  

SET NOCOUNT ON                                        
                                        
	IF(@firstName IS NULL)                                        
	 SET @firstName='%'                                        
	ELSE                                        
	 SET @firstName=@firstName+'%'                                         
	                                        
	IF(@lastName IS NULL)                                        
	 SET @lastName='%'                                        
	ELSE                                        
	 SET @lastName=@lastName+'%' 
	                                        
	IF(@supervisorFirstName IS NULL)                                
	 SET @supervisorFirstName='%'                                
	ELSE                                
	 SET @supervisorFirstName=@supervisorFirstName+'%'                                                              
	IF(@supervisorLastName IS NULL)                                
	 SET @supervisorLastName='%'                                
	ELSE                                
	 SET @supervisorLastName=@supervisorLastName+'%'                 
	                        
	IF(@userName IS NULL)                                        
	 SET @userName='%'                                        
	ELSE                                        
	 SET @userName=@userName+'%'       
	      
	IF  @sortColumn ='Name'                                
	SET @sortColumn='LastName' 

	DECLARE @Temp TABLE ( Id INT, FwkDomainUserId INT, FirstName VARCHAR(64),	
						LastName VARCHAR(64))          
	
	INSERT INTO @Temp            
				--Internal Users having access to COI Module.
				SELECT c.[Id],c.[fwkDomainUserId],c.[firstName],c.[LastName]        
			    FROM dbo.[admperson] c                         
			    INNER JOIN dbo.[FwkApplicationUser] au ON c.[admPersonTypeId] = 1 AND au.[FwkDomainUSerID]=c.[FwkDomainUserId]                     
				AND (au.[ModuleBitmask]IS NOT NULL AND ((au.[ModuleBitmask] & 128)	<> 0 ))                        
	UNION                  
				--External users of COI.       
				SELECT c1.[Id],c1.[fwkDomainUserId],c1.[firstName],c1.[LastName]           
				FROM admPerson c1                   
			    INNER JOIN COI_Hierarchy_Map chm ON c1.[admPersonTypeID]=2 AND c1.[Id]=chm.[childId] 
--IF "OUTER"
IF (@statusXml IS NULL)                                         
BEGIN      
	--IF "INNER-1"                              
	IF  @sortColumn='Supervisor.LastName' OR @sortColumn = 'LastName'  OR @sortColumn=NULL OR @sortColumn = 'Status'                           
	BEGIN                                
		SELECT adp.[Id],coi.[Id]AS COIId,adp1.[Id]AS [SupervisorId]                                 
		FROM  @Temp  adp                         
		LEFT OUTER JOIN  ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]                            
			AND ISNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT ISNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                
		LEFT OUTER JOIN FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                            
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]                                        
		LEFT OUTER JOIN AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                                        
		WHERE                        
		ISNULL(adp.[firstName], '*') LIKE @firstName                                        
		AND                                        
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName 
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName                                        
		AND     
		IsNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation  
															WHEN 1 THEN '%' 
															ELSE '0' 
														 END                                     
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									  WHEN 0 THEN chm.HierarchyTypeId
									  ELSE @HierarchyTypeId 
								  END) 	
		AND
		(
			ISNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																					WHEN 0 THEN '%'                                
																					ELSE '0' 
																				END                      
			AND  ISNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'
																			   END                       
			AND ISNULL(CAST(coi.[HasFamilyAssociations]AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																				END 
		)                               
		AND                                        
		ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected                                 
																	WHEN 0 THEN '%'                                
																	ELSE '1'                                
																   END                        
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'Supervisor.LastName' THEN adp1.[lastName]                                                   
					WHEN 'LastName' THEN adp.[lastName]     
					WHEN 'Status'  THEN coi.Status                                                                   
					ELSE adp.LastName                               
				END                                
		END                                             
		DESC,                                
		CASE @sortOrder                                
		WHEN 1 THEN                               
			CASE @sortColumn                                
				WHEN 'Supervisor.LastName' THEN adp1.[lastName]                              
				WHEN 'LastName' THEN adp.[lastName]       
				WHEN 'Status'  THEN coi.Status                                                                 
				ELSE adp.LastName                                                  
			END                                
		END,           
		CASE @sortOrder                                
			WHEN 0 THEN               
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.FirstName                        
					WHEN 'Supervisor.LastName' THEN adp1.FirstName             
				END                                        
		END           
		DESC,          
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'Supervisor.LastName' THEN adp1.FirstName                              
					WHEN 'LastName' THEN adp.FirstName                                                           
				END                                
		END                           
	END --IF "INNER-1"          
	--IF "INNER-2"    
	ELSE IF (@sortColumn='PolicyViolation' OR @sortColumn='EquityInterest' OR @sortColumn = 'FamilyAssociation' OR @sortColumn = 'OutsideActivity' OR @sortColumn = 'OtherActivity' )                                
	BEGIN                                
		SELECT adp.[Id],coi.[Id]AS COIId ,adp1.[Id] AS [SupervisorId]                
		FROM  @Temp  adp                         
		LEFT OUTER JOIN  ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]                            
			AND IsNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT isNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] AND Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                
		LEFT OUTER JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                                        
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]  
		LEFT OUTER JOIN AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                                        
		WHERE                        
		ISNULL(adp.[firstName], '*') LIKE @firstName                                        
		AND                                        
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName 
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName
		AND
		ISNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation  
															WHEN 1 THEN '%' 
															ELSE '0' 
														 END                                                            
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									  WHEN 0 THEN chm.HierarchyTypeId
									  ELSE @HierarchyTypeId 
								    END) 
		AND
		(                                        
			ISNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected         
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			END                      
			AND IsNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			 END                       
			AND isNULL(CAST(coi.[HasFamilyAssociations] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																					WHEN 0 THEN '%'                                
																					ELSE '0'                                
																				  END 
		)
		AND                                  
		ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected                                 
																		WHEN 0 THEN '%'                       
																		ELSE '1'                                
																	END                                 
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'PolicyViolation' THEN coi.PolicyViolation                                
					WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                                    
					WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                                
					WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                                                    
					WHEN 'OtherActivity' THEN coi.OtherActivity 
					ELSE coi.PolicyViolation                                
				END                                
		END                                             
		DESC,                                
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'PolicyViolation' THEN coi.PolicyViolation                                
					WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                                    
					WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                                
					WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                                                    
					WHEN 'OtherActivity' THEN coi.OtherActivity     
					ELSE coi.PolicyViolation                                
				END                                
			END                                                 
	END--IF "INNER-2" 
	--IF "INNER-3"  
	ELSE IF(@sortColumn='LastNotified' OR @sortColumn='LastSubmitted' OR @sortColumn = 'DateReviewed' )                                
	BEGIN        
		PRINT '1'                         
		SELECT adp.[Id],coi.[Id]AS COIId,adp.[Id]  AS SupervisorId                               
		FROM  @Temp  adp                         
		LEFT OUTER JOIN  ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]                            
			AND IsNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT isNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] AND Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                
		LEFT OUTER JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                                        
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]   
		LEFT OUTER JOIN AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                                     
		WHERE                        
		ISNULL(adp.[firstName], '*') LIKE @firstName                                        
		AND                                        
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName 
		AND  
		IsNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation  
															WHEN 1 THEN '%' 
															ELSE '0' 
														 END                                                                      
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									  WHEN 0 THEN chm.HierarchyTypeId
									  ELSE @HierarchyTypeId 
								  END) 
		AND 
		(
			IsNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                   
																			END                      
			AND  IsNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			  END                       
			AND IsNULL(CAST(coi.[HasFamilyAssociations] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																					WHEN 0 THEN '%'                                
																					ELSE '0'                                
																				 END 
		)                                
		AND                              
		ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected                                 
																		WHEN 0 THEN '%'                                
																		ELSE '1'                                
																		END                                 
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'LastNotified' THEN coi.LastNotified                                
					WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                                    
					WHEN 'DateReviewed' THEN coi.DateReviewed   
					ELSE coi.DateSubmitted                                
				END                                
		END                                             
		DESC,                                
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'LastNotified' THEN coi.LastNotified                                
					WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                                    
					WHEN 'DateReviewed' THEN coi.DateReviewed   
					ELSE coi.DateSubmitted        
				END                                
		END                                
		END     
	END--IF "INNER-3"      
--IF "OUTER"                                              
ELSE  
BEGIN                                 
	DECLARE @xmlDoc INT                                          
	DECLARE @statusTable TABLE (Sequence INT IDENTITY, Status VARCHAR(75))                                          
	EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @statusXml                        
	INSERT INTO @statusTable SELECT [Col1] FROM OPENXML(@xmlDoc, '//Table1',2) WITH (Col1 VARCHAR(75))                                   
	EXEC sp_xml_removedocument @xmlDoc                                  
	--IF "INNER-4" 
	IF @sortColumn='Supervisor.LastName' OR @sortColumn='LastName' OR @sortColumn = 'Status'                            
	BEGIN                                   
		SELECT adp.[Id],coi.[Id]AS COIId,adp1.[Id]AS [SupervisorId]                                          
		FROM  @Temp adp                         
		LEFT OUTER JOIN ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]                            
			AND IsNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT isNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] AND Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                
		LEFT OUTER JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                                  
		INNER JOIN @statusTable sTab ON sTab.[Status]=coi.[Status]                                
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]              
		LEFT OUTER JOIN AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                                 
		WHERE                        
		ISNULL(adp.[firstName], '*') LIKE @firstName                                        
		AND				                                 
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName   
		AND IsNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation  
																WHEN 1 THEN '%' 
																ELSE '0' 
															  END                                                                        
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									  WHEN 0 THEN chm.HierarchyTypeId
									  ELSE @HierarchyTypeId 
								  END) 
		AND                                        
		(
			IsNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			END                       
			AND  IsNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'            
																				ELSE '0'                                
																			   END                       
			AND IsNULL(CAST(coi.[HasFamilyAssociations] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																					WHEN 0 THEN '%'                                
																					ELSE '0'                                
																				  END
		)                                
		AND                                        
			ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected                          
																		WHEN 0 THEN '%'                                
																		ELSE '1'                                
																	   END                                       
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'Supervisor.LastName' THEN adp1.[lastName]                                                  
					WHEN 'LastName' THEN adp.[lastName]     
					WHEN 'Status'  THEN coi.Status                                               
					ELSE adp.[LastName]                                 
				END                                
		END                                             
		DESC,                                
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'Supervisor.LastName' THEN adp1.[lastName]                                                  
					WHEN 'LastName' THEN adp.[lastName]         
					WHEN 'Status'  THEN coi.Status                   
					ELSE adp.[LastName]                                    
				END                                
		END,             
		CASE @sortOrder                                
			WHEN 0 THEN               
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.FirstName                        
					WHEN 'Supervisor.LastName' THEN adp1.FirstName             
				END                                        
		END           
		DESC,          
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'Supervisor.LastName' THEN adp1.FirstName                              
					WHEN 'LastName' THEN adp.FirstName                                                           
				END                                
		END                                             
	END--IF "INNER-4" 
	--IF "INNER-5" 
	ELSE IF(@sortColumn='PolicyViolation' OR @sortColumn='EquityInterest' OR @sortColumn = 'FamilyAssociation' OR @sortColumn = 'OutsideActivity' OR @sortColumn = 'OtherActivity')                                
	BEGIN                   
		SELECT adp.[Id],coi.[Id]AS COIId,adp1.[Id]AS [SupervisorId]                                            
		FROM  @Temp  adp                         
		LEFT OUTER JOIN  ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]                            
			AND IsNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT isNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                
		LEFT JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                                  
		INNER JOIN @statusTable sTab ON sTab.[Status]=coi.[Status]                                
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]               
		LEFT OUTER JOIN  AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                                                   
		WHERE                        
		ISNULL(adp.[firstName], '*') LIKE @firstName                          
		AND                                        
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName    
		AND IsNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation 
																WHEN 1 THEN '%' 
																ELSE '0'	
															END                                                                      
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									WHEN 0 THEN chm.HierarchyTypeId
									ELSE @HierarchyTypeId 
								  END) 
		AND        
		(
			IsNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			END                       
			AND  IsNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			  END                       
			AND IsNULL(CAST(coi.[HasFamilyAssociations] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																					WHEN 0 THEN '%'                                
																					ELSE '0'                                
																				  END
		)                             
		AND                                        
		ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected                                 
																	WHEN 0 THEN '%'                                
																	ELSE '1'                                
																   END                                       
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'PolicyViolation' THEN coi.PolicyViolation                                
					WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                                    
					WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                           
					WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                 
					WHEN 'OtherActivity' THEN coi.OtherActivity
					ELSE coi.PolicyViolation                                
				END                                
		END                                             
		DESC,                                
		CASE @sortOrder                                
			WHEN 1 THEN                        
				CASE @sortColumn                                
					WHEN 'PolicyViolation' THEN coi.PolicyViolation                                
					WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                                    
					WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                                
					WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                
					WHEN 'OtherActivity' THEN coi.OtherActivity          
					ELSE coi.PolicyViolation                                
				END                                
		END                                              
	END--IF "INNER-5"
	--IF "INNER-6"
	ELSE IF(@sortColumn='LastNotified' OR @sortColumn='LastSubmitted' OR @sortColumn = 'DateReviewed')                                
	BEGIN        
		print '2'                        
		SELECT adp.[Id],coi.[Id]AS COIId,adp1.[Id]AS [SupervisorId]                                               
		FROM  @Temp adp                         
		LEFT OUTER JOIN  ConflictOfInterest coi ON adp.[id]=coi.[AdmPersonId]    
			AND IsNULL(CAST(coi.[Id] AS VARCHAR(10)),'*') =(SELECT isNULL(CAST(Max(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)                                        
		LEFT JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]                                  
		INNER JOIN @statusTable sTab ON sTab.[Status]=coi.[Status]                                
		LEFT OUTER JOIN COI_Hierarchy_Map chm ON adp.[Id]= chm.[childId]              
		LEFT OUTER JOIN AdmPerson adp1 ON adp1.[Id]= chm.[ParentId]                           
		WHERE                          
		ISNULL(adp.[firstName], '*') LIKE @firstName                                        
		AND                                        
		ISNULL(adp.[lastName], '*') LIKE @lastName                                        
		AND                                        
		ISNULL(fdu.[userName], '*') LIKE @userName 
		AND                                
		ISNULL(adp1.[lastName], '*') LIKE @supervisorLastName                                
		AND                                
		ISNULL(adp1.[firstName], '*') LIKE @supervisorFirstName   
		AND IsNULL(CAST(chm.Removed AS VARCHAR(1)),'*') LIKE CASE @hasExistingRelation  
																WHEN 1 THEN '%' 
																ELSE '0' 
															 END                                                                       
		AND	chm.HierarchyTypeId = (CASE @HierarchyTypeId
									  WHEN 0 THEN chm.HierarchyTypeId
									  ELSE @HierarchyTypeId 
								  END) 
		AND                                
		(
			IsNULL(CAST(coi.[HasOutsideActivities] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE '0'                                
																			END                       
		AND  IsNULL(CAST(coi.[HasEquityInterests] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																			WHEN 0 THEN '%'                                
																			ELSE '0'                                
																		  END                       
		AND IsNULL(CAST(coi.[HasFamilyAssociations] AS VARCHAR(1)),'*') LIKE CASE @isNothingToDiscloseSelected                                        
																				WHEN 0 THEN '%'                                
																				ELSE                   
																				'0'                                
																			  END
		)                               
		AND                                        
		ISNULL(CAST(coi.[PolicyViolation] AS VARCHAR(1)),'*') LIKE CASE @isPolicyQuestionSelected               
																	WHEN 0 THEN '%'                                
																	ELSE '1'                                
																   END                                       
		ORDER BY                                  
		CASE @sortOrder                                
			WHEN 0 THEN                                 
				CASE @sortColumn                                
					WHEN 'LastNotified' THEN coi.LastNotified                                
					WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                                    
					WHEN 'DateReviewed' THEN coi.DateReviewed    
					ELSE coi.DateSubmitted                                
				END                                
		END                                             
		DESC,          
		CASE @sortOrder                                
			WHEN 1 THEN                                 
				CASE @sortColumn                                
					WHEN 'LastNotified' THEN coi.LastNotified                                
					WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                                    
					WHEN 'DateReviewed' THEN coi.DateReviewed 
					ELSE coi.DateSubmitted                                
				END                                
		END                                
	END--IF "INNER-6"                                 
END--IF "OUTER"                             
END-- Procedure END    


